# _*_ coding:utf-8 _*_
# @Time  : 2023.06.15
# @Author: zizlee
from fastapi import APIRouter, Query, Body
from pydantic import BaseModel, constr, conint, validator
from db_utils import BaseDatabaseConnection
from response import APIResponse
from utils.hutool_utils import StringUtils


class FormulaGroupItem(BaseModel):
    name: constr(min_length=1)


class CreateFormulaGroupItem(FormulaGroupItem):
    parent_id: int
    vcode: str


class UpdateFormulaGroupItem(FormulaGroupItem):
    gid: int
    sort_num: int
    is_active: conint(ge=0, le=1)


class GroupFormulaItem(BaseModel):
    name: str
    unit: str
    formula: str = ''
    fma_explain: str = ''.strip()

    @validator('formula')
    def validate_formula(cls, value):
        # 转为半角
        return StringUtils.full2half(value) if value.strip() else ''

    @validator('fma_explain')
    def validate_fma_explain(cls, value):
        # 转为半角
        return StringUtils.full2half(value) if value.strip() else ''


class CreateGroupFormulaItem(GroupFormulaItem):
    gid: int
    vcode: str


class UpdateGroupFormulaItem(GroupFormulaItem):
    rid: int
    sort_num: int
    is_active: conint(ge=0, le=1)


class FormulaColumnItem(BaseModel):
    name: str
    unit: str = ''


class CreateFormulaColumnItem(FormulaColumnItem):
    formula_id: int
    is_constant: conint(ge=0, le=1)
    constant: float = None
    is_index: conint(ge=0, le=1)
    index_id: int = None


class UpdateFormulaColumnItem(FormulaColumnItem):
    rid: int
    sort_num: int
    is_show: bool
    is_constant: bool
    constant: float = None
    is_index: bool
    index_id: int = None


class FormulaMaintain:

    def __init__(self, vcode: str = None, parent_id: int = 0):
        self.vcode = vcode
        self.parent_id = parent_id

    def create_group(self, g_item: CreateFormulaGroupItem):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("INSERT INTO calcul_group (name,parent_id,vcode,is_active) VALUES (%s,%s,%s,%s);",
                                [g_item.name, self.parent_id, self.vcode, 0])
        return APIResponse.success(msg='创建公式分组成功!')

    def retrieve_formula_group(self):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
                SELECT id,name,parent_id,vcode,sort_num,is_active FROM calcul_group
                WHERE vcode=%s AND parent_id=%s ORDER BY sort_num;
            """, [self.vcode, self.parent_id])
            group_list = conn.cursor.fetchall()
        return APIResponse.success(data={'data': group_list})

    def update_group(self, g_item: UpdateFormulaGroupItem):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("UPDATE calcul_group SET name=%s,sort_num=%s,is_active=%s WHERE id=%s LIMIT 1;",
                                [g_item.name, g_item.sort_num, g_item.is_active, g_item.gid])
        return APIResponse.success(msg='更新分组完成!')


class GroupFormula:
    def __init__(self, gid: int = None):
        self.gid = gid

    def get_group_formula(self):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
               SELECT id,group_id,name,formula,fma_explain,unit,vcode,sort_num,is_active 
               FROM calcul_group_formula WHERE group_id=%s ORDER BY sort_num;
            """, [self.gid])
            ret = list(conn.cursor.fetchall())
        return APIResponse.success(data={'data': ret})

    def create_group_formula(self, item: CreateGroupFormulaItem):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
               INSERT INTO calcul_group_formula (group_id,name,formula,fma_explain,unit,vcode,is_active)
               VALUES (%s,%s,%s,%s,%s,%s,%s);
            """, [item.gid, item.name, item.formula, item.fma_explain, item.unit, item.vcode, 0])
        return APIResponse.success(msg='创建新公式完成')

    def update_group_formula(self, item: UpdateGroupFormulaItem):
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
                UPDATE calcul_group_formula SET name=%s,formula=%s,fma_explain=%s,unit=%s,sort_num=%s,is_active=%s
                WHERE id=%s LIMIT 1;
            """, [item.name, item.formula, item.fma_explain, item.unit, item.sort_num, item.is_active, item.rid])
        return APIResponse.success()


class FormulaColumn:
    def retrieve_columns(self, formula_id: int):
        sql = "SELECT id,formula_id,name,unit,is_constant,constant,is_index,index_id,unit,sort_num,is_show FROM calcul_formula_column WHERE formula_id=%s;"
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, [formula_id])
            columns = list(conn.cursor.fetchall())
        return APIResponse.success(data={'data': columns})

    def create_column(self, item: CreateFormulaColumnItem):
        if item.is_constant:
            item.is_index = 0
            item.index_id = None
        if item.is_index:
            item.is_constant = 0
            item.constant = None

        if not item.is_constant and not item.is_index:
            return APIResponse.validate_error(detail='常数和指标必须至少选定一个!')
        sql = """
            INSERT INTO calcul_formula_column (formula_id,name,is_constant,constant,is_index,index_id,unit)
            VALUES (%s,%s,%s,%s,%s,%s,%s);
        """
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, [item.formula_id, item.name, item.is_constant, item.constant,
                                      item.is_index, item.index_id, item.unit])
        return APIResponse.success(msg='添加待计算成功!')

    def update_column(self, item: UpdateFormulaColumnItem):
        if item.is_constant:
            item.is_index = 0
            item.index_id = None
        if item.is_index:
            item.is_constant = 0
            item.constant = None
        if all([item.is_constant, item.is_index]) or all([not item.is_constant, not item.is_index]):
            return APIResponse.validate_error(detail='不能常数和指标都选或都不选!')
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("""
                UPDATE calcul_formula_column SET name=%s,unit=%s,sort_num=%s,is_show=%s,
                is_constant=%s,constant=%s,is_index=%s,index_id=%s WHERE id=%s LIMIT 1;
            """, [item.name, item.unit, item.sort_num, item.is_show, item.is_constant, item.constant, item.is_index, item.index_id, item.rid])
        return APIResponse.success(msg='修改待计算成功!')


formula_api = APIRouter()


# 创建一个公式分组
@formula_api.post('/group/create/')
def create_formula_group(g_item: CreateFormulaGroupItem = Body(...)):
    return FormulaMaintain(vcode=g_item.vcode, parent_id=g_item.parent_id).create_group(g_item=g_item)


# 查询公式分组
@formula_api.get('/group/retrieve/')
def retrieve_formula_group(vcode: str = Query(...), parent: int = Query(0)):
    return FormulaMaintain(vcode=vcode, parent_id=parent).retrieve_formula_group()


# 更新公式分组
@formula_api.put('/group/update/')
def update_index_group(g_item: UpdateFormulaGroupItem = Body(...)):
    return FormulaMaintain().update_group(g_item=g_item)


# 查询组内的公式
@formula_api.get('/groupFormula/retrieve/')
def get_group_index(gid: int = Query(...)):
    return GroupFormula(gid=gid).get_group_formula()


# 创建组内公式
@formula_api.post('/groupFormula/create/')
def create_group_index(item: CreateGroupFormulaItem = Body(...)):
    return GroupFormula().create_group_formula(item=item)


@formula_api.put('/groupFormula/update/')  # 更新组内的公式
def update_group_index(item: UpdateGroupFormulaItem = Body(...)):
    return GroupFormula().update_group_formula(item=item)


# 查询公式计算项目详情
@formula_api.get('/formulaColumns/retrieve/')
def get_formula_column(fid: int = Query(...)):
    return FormulaColumn().retrieve_columns(formula_id=fid)


# 创建公式计算项目
@formula_api.post('/formulaColumns/create/')
def create_formula_column(item: CreateFormulaColumnItem = Body(...)):
    return FormulaColumn().create_column(item=item)


# 修改公式计算项目
@formula_api.put('/formulaColumns/update/')
def create_formula_column(item: UpdateFormulaColumnItem = Body(...)):
    return FormulaColumn().update_column(item=item)
